﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using MySql.Data.MySqlClient;
//using System.Data.SqlClient;


namespace Database_Ass1_2013
{
    public partial class frmCarManagement : Form
    {
        DatabaseConnection theConnection;

        #region First load

        public frmCarManagement()
        {
            InitializeComponent();
        }

        #endregion

        #region Datagrid load

        //this function will load all main the data grids in each tab
        private void frmCarManagement_Load(object sender, EventArgs e)
        {
            // TODO: This line of code loads data into the 'tFBSUniversityDataSet2.EMPLOYEE' table. You can move, or remove it, as needed.
            this.eMPLOYEETableAdapter.FillAllEployees(this.tFBSUniversityDataSet2.EMPLOYEE);
            // TODO: This line of code loads data into the 'tFBSUniversityDataSet1.DEPARTMENT' table. You can move, or remove it, as needed.
            this.dEPARTMENTTableAdapter.Fill(this.tFBSUniversityDataSet1.DEPARTMENT);
        }

        #endregion

        #region Click on data grids
        
        private void dataGridEmployees_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            txtEmployeeID.Text = dataGridEmployees[0, e.RowIndex].Value.ToString();
            txtEmpFirstname.Text = dataGridEmployees[2, e.RowIndex].Value.ToString();
            txtEmpSurname.Text = dataGridEmployees[1, e.RowIndex].Value.ToString();
            cmbEmpRole.Text = dataGridEmployees[5, e.RowIndex].Value.ToString();
            txtEmailAddress.Text = dataGridEmployees[3, e.RowIndex].Value.ToString();
            txtContactNumber.Text = dataGridEmployees[4, e.RowIndex].Value.ToString();
            cmbEmpDepartment.Text = dataGridEmployees[6, e.RowIndex].Value.ToString();

            txtEmployeeID.ReadOnly = true;
            txtEmpFirstname.ReadOnly = true;
            txtEmpSurname.ReadOnly = true;
            txtEmailAddress.ReadOnly = true;
            txtContactNumber.ReadOnly = true;
            cmbEmpRole.Enabled = false;
            cmbEmpDepartment.Enabled = false;
        }

        #endregion
        
        #region Combo Boxes Fill

        //private void cmbResVehicleType_Click(object sender, EventArgs e)
        //{
        //    cmbResVehicleType.Items.Clear();
        //    string aVehicleModelList = "SELECT DISTINCT(Model) as Model FROM VEHICLE";
        //    fillListBox(cmbResVehicleType, aVehicleModelList, "Model");
        //}

        //private void cmbResPlateNo_Click(object sender, EventArgs e)
        //{
        //    cmbResPlateNo.Items.Clear(); //clear the combo box before filling in
        //    //MessageBox.Show(cmbResVehicleType.SelectedItem.ToString());
        //    if (cmbResVehicleType.SelectedItem.ToString() != "")
        //    {
        //        string aRegNoAvailableList = "SELECT RegNo FROM VEHICLE WHERE Model = '" + cmbResVehicleType.SelectedItem.ToString() + "'";
        //        fillListBox(cmbResPlateNo, aRegNoAvailableList, "RegNo");
        //    }
        //}
        
        private void cmbEmpRole_Click(object sender, EventArgs e)
        {
            cmbEmpRole.Items.Clear(); //clear the combo box before filling
            string empRolesQuerie = "SELECT DISTINCT(Role) FROM EMPLOYEE";
            fillListBox(cmbEmpRole, empRolesQuerie, "Role");
        }

        private void cmbEmpDepartment_Click(object sender, EventArgs e)
        {
            cmbEmpDepartment.Items.Clear(); //clear the box beforfe filling
            string empDepartmentList = "SELECT Name FROM DEPARTMENT";
            fillListBox(cmbEmpDepartment, empDepartmentList, "Name");
        }

        #endregion

        #region Events

        private int createNewId(string aTable, string aColumn)
        {
            int aNewId = 0; // the new id to be set to the member

            //get the id for the new member to be created
            try
            {
                string aNewIdQuery = "SELECT MAX(" + aColumn + ") FROM " + aTable + "";
                theConnection = new DatabaseConnection(aNewIdQuery);
                while (theConnection.getDataReader().Read())
                {
                    aNewId = theConnection.getDataReader().GetInt32("MAX(" + aColumn + ")") + 1;
                }
                theConnection.getDataReader().Close(); //close the connection 
            }
            catch (Exception ex)
            {
                MessageBox.Show("Could not obtain the new id due to the following error: \n " +
                                ex.ToString());
            }
            finally
            {
                theConnection.getMySqlConnection().Close(); //close the connection
            }

            return aNewId;
        }

        private void tabMenu_Selected(object sender, TabControlEventArgs e)
        {
            switch (e.TabPageIndex)
            {
                case 2:
                    //txtResId.Text = createNewId("BOOKING", "BookingId").ToString();
                    break;
                default:
                    //default tab
                    break;
            }
        }

        //private void btnCreateReservation_Click(object sender, EventArgs e)
        //{
        //    //insert the new member
        //    try
        //    {
        //        DateTime aResDate = DateTime.Parse(txtResDepDate.Text);
        //        string aResDepDate = aResDate.Date.ToString("yyyy/MM/dd");

        //        string mfName, msName;
        //        string[] mamberName = cmbResMemName.SelectedItem.ToString().Split(' ');
        //        mfName = mamberName[0];
        //        msName = mamberName[1];

        //        string efName, esName;
        //        string[] employeeName = cmbResClerk.SelectedItem.ToString().Split(' ');
        //        efName = employeeName[0];
        //        esName = employeeName[1];

        //        string depName = cmbResDepartment.SelectedValue.ToString();
        //        string vehicleType = cmbResVehicleType.SelectedItem.ToString();
        //        string vehicleRegNo = cmbResPlateNo.SelectedItem.ToString();

        //        //insert the new member
        //        string aBookingInsert = "INSERT INTO BOOKING VALUES (" + txtResId.Text + ", "
        //                                               + "'" + aResDepDate + "', "
        //                                               + "'" + txtResDestAddress.Text + "', "
        //                                               + "'" + txtResPostcode.Text + "', "
        //                                               + "(SELECT EmployeeId FROM EMPLOYEE as e WHERE e.FirstName= '" + efName + "' AND e.Surname= '" + esName + "')" + ","
        //                                               + "(SELECT DepartmentId FROM DEPARTMENT as d WHERE d.Name= '" + depName + "')" + ","
        //                                               + "(SELECT VahicleId FROM VEHICLE as v WHERE v.Model= '" + vehicleType + "' AND v.RegNo= '" + vehicleRegNo + "')" + ","
        //                                               + "(SELECT MemberId FROM MEMBER as m WHERE m.FirstName= '" + mfName + "' AND m.Surname= '" + msName + "')"
        //                                               + ")";

        //        theConnection = new DatabaseConnection(aBookingInsert);
        //        MessageBox.Show("Member created!");
        //    }
        //    catch (Exception ex)
        //    {
        //        MessageBox.Show("Could not create a new member due to the following error: \n " +
        //                        ex.ToString());
        //    }
        //    finally
        //    {
        //        theConnection.getMySqlConnection().Close(); //close the connection
        //    }
        //}

        //private void txtResDepDate_Click(object sender, EventArgs e)
        //{
        //    txtResDepDate.ForeColor = Color.Black;
        //}

        private void btnEditEmp_Click(object sender, EventArgs e)
        {
            txtEmpFirstname.ReadOnly = false;
            txtEmpSurname.ReadOnly = false;
            txtEmailAddress.ReadOnly = false;
            txtContactNumber.ReadOnly = false;
            cmbEmpRole.Enabled = true;
        }

        private void btnDeleteEmp_Click(object sender, EventArgs e)
        {
            if(!txtEmployeeID.Text.Equals(""))
            {
                if (MessageBox.Show(this, "Would you like to delete the Employee", "Deleting employee...",
                        MessageBoxButtons.YesNo, MessageBoxIcon.Question)
                        == DialogResult.Yes)
                {
                    //get the id for the new member to be created
                    try
                    {

                        string deleteEmp = "DELETE FROM EMPLOYEE WHERE EmployeeId = " + txtEmployeeID.Text;
                        theConnection = new DatabaseConnection(deleteEmp);
                        MessageBox.Show("Employee deleted!");
                        theConnection.getMySqlConnection().Close();
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show("Could not obtain the new id due to the following error: \n " +
                                        ex.ToString());
                    }
                    finally
                    {
                        theConnection.getMySqlConnection().Close(); //close the connection
                    }
                }
                //reload the data grid 
                this.tFBSUniversityDataSet2.Clear();
                this.eMPLOYEETableAdapter.FillAllEployees(this.tFBSUniversityDataSet2.EMPLOYEE);
            }
            else
            {
                MessageBox.Show("No employee selected!");
            }
        }

        private void btnCreateEmp_Click(object sender, EventArgs e)
        {
            txtEmpFirstname.ReadOnly = false;
            txtEmpSurname.ReadOnly = false;
            txtEmailAddress.ReadOnly = false;
            txtContactNumber.ReadOnly = false;
            cmbEmpRole.Enabled = true;
            cmbEmpDepartment.Enabled = true;

            txtEmployeeID.Text = "";
            txtEmpFirstname.Text = "";
            txtEmpSurname.Text = "";
            txtEmailAddress.Text = "";
            txtContactNumber.Text = "";
            cmbEmpRole.Text = "";
            cmbEmpDepartment.Text = "";
            
        }
        
        private void btnSaveEmp_Click(object sender, EventArgs e)
        {
            if (!txtEmployeeID.Text.Equals(""))
            {
                if (MessageBox.Show(this, "Would you like to update the Employee", "Updating employee...",
                        MessageBoxButtons.YesNo, MessageBoxIcon.Question)
                        == DialogResult.Yes)
                {
                    string editEmp = "UPDATE EMPLOYEE SET FirstName = '" + txtEmpFirstname.Text + "', "
                                                          + "Surname = '" + txtEmpSurname.Text + "', "
                                                          + "EmailAddress = '" + txtEmailAddress.Text + "', "
                                                          + "TelNo = '" + txtContactNumber.Text + "', "
                                                          + "Role = '" + cmbEmpRole.Text + "' "
                                                          + "WHERE EmployeeId = " + txtEmployeeID.Text + " ";

                    theConnection = new DatabaseConnection(editEmp);
                    MessageBox.Show("Employee updated!");
                    theConnection.getMySqlConnection().Close();
                }
            }
            else {
                if (MessageBox.Show(this, "Would you like to create the Employee", "Creating employee...",
                        MessageBoxButtons.YesNo, MessageBoxIcon.Question)
                        == DialogResult.Yes)
                {
                    string createEmp = "INSERT INTO EMPLOYEE VALUES ('NULL', '" + txtEmpFirstname.Text + "',"
                                                          + "'" + txtEmpSurname.Text + "',"
                                                          + "'" + txtEmailAddress.Text + "',"
                                                          + "'" + txtContactNumber.Text + "',"
                                                          + "'" + cmbEmpRole.Text + "',"
                                                          + "(SELECT DepartmentId FROM DEPARTMENT WHERE Name = '"+cmbEmpDepartment.SelectedItem.ToString()+"'))";
                    theConnection = new DatabaseConnection(createEmp);
                    MessageBox.Show("Employee created!");
                    theConnection.getMySqlConnection().Close();
                }
            }
            //reload the data grid 
            this.tFBSUniversityDataSet2.Clear();
            this.eMPLOYEETableAdapter.FillAllEployees(this.tFBSUniversityDataSet2.EMPLOYEE);
            resetFields();

        }
        
        #endregion
        
        #region Custom functions
            //generic fill list box
            private void fillListBox(ComboBox cb, string query, string column)
            {
                cb.Items.Clear(); //clear the combo box before filling in
                try
                {
                    /*retreive all the reg numbers of those vehicles 
                     * matching the model selected from the combo box*/
                    theConnection = new DatabaseConnection(query);
                    while (theConnection.getDataReader().Read())
                    {
                        //dinamically add the RegNo to the dropdown list
                        cb.Items.Add(theConnection.getDataReader().GetString(column));
                    }
                }
                catch (Exception ex)
                {

                }
                finally
                {
                    theConnection.getMySqlConnection().Close(); //close the connection
                }
            }

            private void resetFields() {
                txtEmpFirstname.ReadOnly = true;
                txtEmpSurname.ReadOnly = true;
                txtEmailAddress.ReadOnly = true;
                txtContactNumber.ReadOnly = true;
                cmbEmpRole.Enabled = false;
                cmbEmpDepartment.Enabled = false;

                txtEmployeeID.Text = "";
                txtEmpFirstname.Text = "";
                txtEmpSurname.Text = "";
                txtEmailAddress.Text = "";
                txtContactNumber.Text = "";
                cmbEmpRole.Text = "";
                cmbEmpDepartment.Items.Clear();
            }
        #endregion

                           

    }
}
